package org.iabako.server.tools;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by jose on 22/01/15.
 */
public class IabakoWorkbook {

    static Logger log = Logger.getLogger(IabakoWorkbook.class.getName());

    private Workbook workbook;
    private Map<String, CellStyle> styles;
    private List<Sheet> sheets = new ArrayList<Sheet>();
    private List<HeaderDefinition> headerDefinitions = new ArrayList<HeaderDefinition>();

    private String fileName;

    public IabakoWorkbook(String fileName, String firstSheetName, String enterpriseName) {

        formatWorkbook(firstSheetName, enterpriseName);
        this.fileName = fileName;
    }

    public void addHeaderDefinition(int excelColumnWidth, String label) {
        headerDefinitions.add(new HeaderDefinition(excelColumnWidth, label));
    }

    public class HeaderDefinition {
        private int size;
        private String label;

        public HeaderDefinition(int size, String label) {
            this.size = size;
            this.label = label;
        }
    }

    public List<Sheet> getSheets() {
        return sheets;
    }

    public Map<String, CellStyle> getStyles() {
        return styles;
    }

    private void formatWorkbook(String firstSheetName, String enterpriseName) {

        workbook = new HSSFWorkbook();
        styles = createStyles(workbook);
        Sheet sheet = workbook.createSheet(firstSheetName);
        sheets.add(sheet);

        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setLandscape(true);
        printSetup.setHeaderMargin(0.3);
        printSetup.setLeftToRight(true);

        //sheet header
        sheet.getHeader().setCenter(HSSFHeader.fontSize((short) 16) + HSSFHeader.startBold() + enterpriseName + HSSFHeader.endBold());

        //sheet footer
        sheet.getFooter().setLeft(HSSFFooter.fontSize((short) 8) + " " + new SimpleDateFormat("dd/MM/yyyy HH:mm").format(new Date()));
        sheet.getFooter().setRight(HSSFFooter.font("Arial", "italic") + HSSFFooter.fontSize((short) 8) + "www.iabako.com");
        sheet.getFooter().setCenter(HSSFFooter.fontSize((short) 8) + HSSFFooter.page() + " / " + HSSFFooter.numPages());

        //sheet margin
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setMargin(Sheet.TopMargin, 0.7);
        sheet.setMargin(Sheet.BottomMargin, 0.6);
        sheet.setMargin(Sheet.RightMargin, 0.6);
        sheet.setMargin(Sheet.LeftMargin, 0.6);
    }

    public void addHeader(Sheet sheet, boolean smallFont, int rowIndex) {
        Row headerRow = sheet.createRow(rowIndex);
        headerRow.setHeightInPoints(30);
        Cell headerCell;

        int indexColumns = 0;
        for (HeaderDefinition headerDefinition : headerDefinitions) {
            sheet.setColumnWidth(indexColumns, headerDefinition.size);
            headerCell = headerRow.createCell(indexColumns++);
            headerCell.setCellValue(headerDefinition.label);
            if (smallFont) {
                headerCell.setCellStyle(styles.get("headerSmall"));
            } else {
                headerCell.setCellStyle(styles.get("header"));
            }
        }

        sheet.createFreezePane(0, rowIndex + 1);
        sheet.setRepeatingRows(new CellRangeAddress(0, rowIndex, 0, headerDefinitions.size() - 1));
    }

    /**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();

        CellStyle style = wb.createCellStyle();
        style.setWrapText(true);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setDataFormat(wb.createDataFormat().getFormat("@"));
        styles.put("cell", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cell"));
        Font smallFont = wb.createFont();
        smallFont.setFontHeightInPoints((short) 8);
        style.setFont(smallFont);
        styles.put("cellSmall", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cellSmall"));
        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        styles.put("cellSmallGray", style);

        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cell"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(headerFont);
        style.setWrapText(true);
        styles.put("header", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("header"));
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());
        styles.put("headerDarker", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("header"));
        Font smallWhiteFont = wb.createFont();
        smallWhiteFont.setFontHeightInPoints((short) 9);
        smallWhiteFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(smallWhiteFont);
        styles.put("headerSmall", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cell"));
        style.setAlignment(CellStyle.ALIGN_CENTER);
        styles.put("cellCenter", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cell"));
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setDataFormat(wb.createDataFormat().getFormat("# ##0.00"));
        styles.put("cellAmount", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cellAmount"));
        style.setFont(smallFont);
        styles.put("cellAmountSmall", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cellAmountSmall"));
        style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("cellAmountSmallBlue", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cellAmountSmall"));
        style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        styles.put("cellAmountSmallYellow", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cellCenter"));
        style.setDataFormat(wb.createDataFormat().getFormat("dd/MM/yyyy"));
        styles.put("cellDate", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("cellDate"));
        style.setFont(smallFont);
        styles.put("cellDateSmall", style);

        return styles;
    }

    public void fillCell(Cell cell, CellStyle cellStyle, Object value) {
        if (value instanceof Double) {
            if (cellStyle == null) {
                cellStyle = styles.get("cellAmount");
            }
            cell.setCellValue((Double) value);
        } else if (value instanceof Date) {
            if (cellStyle == null) {
                cellStyle = styles.get("cellDate");
            }

            cell.setCellValue((Date) value);
        } else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        } else {
            cell.setCellValue(value != null ? value.toString() : null);
        }

        cell.setCellStyle(cellStyle == null ? styles.get("cell") : cellStyle);
    }

    public String addFileToSession() {
        // Write the output to a tmp file
        File tmpFile;
        try {
            tmpFile = File.createTempFile("iabako_excel_file", ".xls");
            FileOutputStream out = new FileOutputStream(tmpFile);
            workbook.write(out);
            out.close();

        } catch (IOException e) {
            log.error(e.getMessage(), e);
            return null;
        }

        FileTools.addFileToSession(tmpFile, fileName + ".xls");

        return tmpFile.getName();
    }
}
